import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.text.SimpleDateFormat;
import java.text.DateFormat;

public class Showlist
{
	public static void adminList() throws SQLException
	{
		Connector conn = new Connector("select * from user where role = 'ADMIN'");
		ResultSet rs = conn.execute();
		System.out.println("--------------------");
		System.out.println("Administrator List");
		System.out.println("--------------------");
		System.out.println("UserID\t\tUsername");
		System.out.println("--------------------");
		while(rs.next())
		{
			int userid = rs.getInt("userid");
			String username = rs.getString("username");
			System.out.println(userid + "\t\t" + username);
		}
		rs.close();
	}

	public static void staffList() throws SQLException
	{
		Connector conn = new Connector("select * from user where role = 'STAFF'");
		ResultSet rs = conn.execute();
		System.out.println("--------------------");
		System.out.println("Staff List");
		System.out.println("--------------------");
		System.out.println("UserID\t\tUsername");
		System.out.println("--------------------");
		while(rs.next())
		{
			int userid = rs.getInt("userid");
			String username = rs.getString("username");
			System.out.println(userid + "\t\t" + username);
		}
		rs.close();
	}
	
	public static void nonuniList() throws SQLException
	{
		Connector conn = new Connector("select * from user where role = 'NONUNI'");
		ResultSet rs = conn.execute();
		System.out.println("--------------------");
		System.out.println("Non-uni Customer List");
		System.out.println("--------------------");
		System.out.println("UserID\t\tUsername");
		System.out.println("--------------------");
		while(rs.next())
		{
			int userid = rs.getInt("userid");
			String username = rs.getString("username");
			System.out.println(userid + "\t\t" + username);
		}
		rs.close();
	}
	
	public static void studentList() throws SQLException
	{
		Connector conn = new Connector("select * from user where role = 'CUSTOMER'");
		ResultSet rs = conn.execute();
		System.out.println("--------------------");
		System.out.println("Customer List");
		System.out.println("--------------------");
		System.out.println("UserID\t\tUsername");
		System.out.println("--------------------");
		while(rs.next())
		{
			int userid = rs.getInt("userid");
			String username = rs.getString("username");
			System.out.println(userid + "\t\t" + username);
		}
		rs.close();
	}
	
	
	public static void facilityList() throws SQLException
	{
		Connector conn = new Connector("select * from facility");
		ResultSet rs = conn.execute();
		System.out.println("--------------------");
		System.out.println("Facility List");
		System.out.println("--------------------");
		System.out.println("Facility ID\t\tFacility Name");
		System.out.println("-------------------------");
		while(rs.next())
		{
			int facilityid = rs.getInt("facilityid");
			String facilityname = rs.getString("facilityname");
			System.out.println(facilityid + "\t\t" + facilityname);
		}
		rs.close();
	}
	
	public static void venueList(int fid) throws SQLException
	{
		Connector conn = new Connector("select * from venue where facilityid=" + fid);
		ResultSet rs = conn.execute();
		System.out.println("--------------------");
		System.out.println("Venue List");
		System.out.println("--------------------");
		System.out.println("Venue ID\t\tVenue Name");
		System.out.println("-------------------------");
		while(rs.next())
		{
			int venueid = rs.getInt("venueid");
			String venuename = rs.getString("venuename");
			System.out.println(venueid + "\t\t" + venuename);
		}
		rs.close();
	}
	
	public static void reserveList(int fid) throws SQLException
	{
		Connector conn = new Connector("select * from reserve,facility where facility.facilityid=reserve.facilityid and facility.facilityid=" + fid);
		ResultSet rs = conn.execute();
		System.out.println("--------------------");
		System.out.println("Reserve list");
		System.out.println("--------------------");
		System.out.println("Reserve ID\tFacility Name\t\tTime");
		System.out.println("-------------------------");
		while(rs.next())
		{
			int bookingid = rs.getInt("reserveid");
			String fn = rs.getString("facilityname");
			String bs = rs.getString("reservedate");
			System.out.println(bookingid + "\t\t" + fn + "\t\t" + bs);
		}
		rs.close();
	}
	
	public static void bookingList(int uid) throws SQLException
	{
		Connector conn = new Connector("select * from booking,venue,facility where venue.venueid=booking.venueid and facility.facilityid=booking.facilityid and userid=" + uid);
		ResultSet rs = conn.execute();
		System.out.println("--------------------");
		System.out.println("Booking list");
		System.out.println("--------------------");
		System.out.println("Booking ID\tFacility Name\t\tVenue Name\t\tTime");
		System.out.println("-------------------------");
		while(rs.next())
		{
			int bookingid = rs.getInt("bookingid");
			String fn = rs.getString("facilityname");
			String vn = rs.getString("venuename");
			String bs = rs.getString("bookingstart");
			System.out.println(bookingid + "\t\t" + fn + "\t\t" + vn + "\t\t" + bs);
		}
		rs.close();
	}

	public static void bookingSearch(int fid, Date da) throws SQLException
	{
		Timestamp ds = new Timestamp(da.getTime());
		DateFormat outFormat = new SimpleDateFormat("yyyy-MM-dd");
		Connector conn = new Connector("select facility.facilityname,venue.venuename,(select count(*) from booking where facilityid = facility.facilityid and venueid = venue.venueid and DATE_FORMAT(bookingstart,'YYYY-MM-DD') = DATE_FORMAT('" + ds + "','YYYY-MM-DD')) from facility,venue where facility.facilityid = " + fid);
		ResultSet rs = conn.execute();
		System.out.println("--------------------");
		System.out.println("Search Result");
		System.out.println("--------------------");
		System.out.println("Date\t\tFacility Name\t\tVenue Name\t\tBookings");
		System.out.println("-------------------------");
		while(rs.next())
		{
			String fn = rs.getString("facilityname");
			String vn = rs.getString("venuename");
			int count = rs.getInt(3);
			System.out.println(outFormat.format(da) + "\t" + fn + "\t\t" + vn + "\t\t" + count);
		}
		rs.close();
	}
}
